﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using ServiceThuVien.Model;
using ServiceThuVien.LinkModel;

namespace ServiceThuVien
{
    public class SQLServerSelect
    {
        SqlConnection cn;
        public SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();

        private string GetConnection()
        {
            builder.ConnectionString = "server=RADO-PC\\SQLEXPRESS;user id=sa;" +
            "password= 123;initial catalog=QLTV";
            return builder.ConnectionString;
        }

        private void ConnectSQL(ref SqlConnection cn, string pName, ref SqlCommand cmd)
        {
            cn = new SqlConnection(GetConnection());
            string procName = pName;
            cmd = new SqlCommand();
            cmd.CommandText = procName;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = cn;
        }

        //public DataSet SelectTacGia_HoTen(string HoTenTacGia)
        //{
        //    string procName = "SelectTacGia_HoTen";

        //    SqlCommand cmd = new SqlCommand();
            
        //    ConnectSQL(ref cn, procName, ref cmd);
            
        //    DataSet ds = new DataSet();

        //    cmd.Parameters.AddWithValue("@HoTenTacGia", HoTenTacGia);
        //    try
        //    {
        //        cn.Open();
        //        SqlDataAdapter adap = new SqlDataAdapter(cmd);
        //        adap.Fill(ds);
        //        
        //            cn.Close();
        //        return ds;
        //    }
        //    catch
        //    {
        //        return null;
        //    }
        //}

        public List<TacGia> SelectTacGia_HoTen(string HoTenTacGia)
        {
            string procName = "SelectTacGia_HoTen";

            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            DataSet ds = new DataSet();
            List<TacGia> dsTacGia = new List<TacGia>();

            cmd.Parameters.AddWithValue("@HoTenTacGia", HoTenTacGia);
            try
            {
                cn.Open();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(ds);
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    TacGia temp = new TacGia();
                    temp.MaTacGia = ds.Tables[0].Rows[i][0].ToString();
                    temp.HoTenTacGia = ds.Tables[0].Rows[i][1].ToString();
                    temp.DiaChi = ds.Tables[0].Rows[i][2].ToString();

                    dsTacGia.Add(temp);
                }
                cn.Close();
                return dsTacGia;
            }
            catch
            {
                return null;
            }
        }


        //public DataSet SelectSach_TacGia(string HoTenTacGia)
        //{
        //    string procName = "SelectSach_TacGia";

        //    SqlCommand cmd = new SqlCommand();

        //    ConnectSQL(ref cn, procName, ref cmd);

        //    DataSet ds = new DataSet();

        //    cmd.Parameters.AddWithValue("@HoTenTacGia", HoTenTacGia);
        //    try
        //    {
        //        cn.Open();
        //        SqlDataAdapter adap = new SqlDataAdapter(cmd);
        //        adap.Fill(ds);
        //        cn.Close();
        //        return ds;
        //    }
        //    catch
        //    {
        //        return null;
        //    }
        //}

        public List<DSTimSach> SelectSach_TacGia(string HoTenTacGia)
        {
            string procName = "SelectSach_TacGia";

            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            DataSet ds = new DataSet();
            List<DSTimSach> dsSach = new List<DSTimSach>();

            cmd.Parameters.AddWithValue("@HoTenTacGia", HoTenTacGia);
            try
            {
                cn.Open();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    DSTimSach temp = new DSTimSach();
                    temp.MaSach = ds.Tables[0].Rows[i][0].ToString();
                    temp.MaDauSach = ds.Tables[0].Rows[i][1].ToString();
                    temp.TenDauSach = ds.Tables[0].Rows[i][2].ToString();
                    temp.HoTenTacGia = ds.Tables[0].Rows[i][3].ToString();
                    temp.TenTheLoai = ds.Tables[0].Rows[i][4].ToString();
                    temp.NhaXuatBan = ds.Tables[0].Rows[i][5].ToString();
                    temp.NamXuatBan = Int32.Parse(ds.Tables[0].Rows[i][6].ToString());
                    temp.DonGia = Int32.Parse(ds.Tables[0].Rows[i][7].ToString());
                    
                    dsSach.Add(temp);
                }

                cn.Close();
                return dsSach;
            }
            catch
            {
                return null;
            }
        }

        public DSSach SelectThongTinSach_MaSach(string MaSach)
        {
            string procName = "SelectThongTinSach_MaSach";

            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            DataSet ds = new DataSet();

            cmd.Parameters.AddWithValue("@MaSach", MaSach);
            try
            {
                cn.Open();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(ds);
                DSSach temp = new DSSach();
                temp.MaSach = ds.Tables[0].Rows[0][0].ToString();
                temp.MaDauSach = ds.Tables[0].Rows[0][1].ToString();
                temp.TenDauSach = ds.Tables[0].Rows[0][2].ToString();
                temp.TenTheLoai = ds.Tables[0].Rows[0][3].ToString();
                temp.HoTenTacGia = ds.Tables[0].Rows[0][4].ToString();
                temp.NhaXuatBan = ds.Tables[0].Rows[0][5].ToString();
                temp.NamXuatBan = Int32.Parse(ds.Tables[0].Rows[0][6].ToString());


                cn.Close();
                return temp;
            }
            catch
            {
                return null;
            }
        }

        //public DataSet SelectSach_TenDauSach(string TenDauSach)
        //{
        //    string procName = "SelectSach_TenDauSach";

        //    SqlCommand cmd = new SqlCommand();

        //    ConnectSQL(ref cn, procName, ref cmd);

        //    DataSet ds = new DataSet();

        //    cmd.Parameters.AddWithValue("@TenDauSach", TenDauSach);
        //    try
        //    {
        //        cn.Open();
        //        SqlDataAdapter adap = new SqlDataAdapter(cmd);
        //        adap.Fill(ds);
        //        cn.Close();
        //        return ds;
        //    }
        //    catch
        //    {
        //        return null;
        //    }
        //}

        public List<DSDocGia> SelectDocGia(string HoTenDocGia)
        {
            string procName = "SelectDocGia";

            SqlCommand cmd = new SqlCommand();
            ConnectSQL(ref cn, procName, ref cmd);
            DataSet ds = new DataSet();
            List<DSDocGia> dsDocGia = new List<DSDocGia>();
            cmd.Parameters.AddWithValue("@HoTenDocGia", HoTenDocGia);
            try
            {
                cn.Open();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(ds);

                

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    DSDocGia temp = new DSDocGia();
                    temp.MaDocGia = ds.Tables[0].Rows[i][0].ToString();
                    temp.HoTenDocGia = ds.Tables[0].Rows[i][1].ToString();
                    temp.TenLoaiDocGia = ds.Tables[0].Rows[i][2].ToString();
                    temp.NgaySinh = DateTime.Parse(ds.Tables[0].Rows[i][3].ToString());
                    temp.DiaChi = ds.Tables[0].Rows[i][4].ToString();
                    temp.Email = ds.Tables[0].Rows[i][5].ToString();
                    temp.NgayLapThe = DateTime.Parse(ds.Tables[0].Rows[i][6].ToString());
                    temp.NgayHetHan = DateTime.Parse(ds.Tables[0].Rows[i][7].ToString());

                    dsDocGia.Add(temp);
                }

                cn.Close();
                return dsDocGia;
            }
            catch
            {
                return null;
            }
        }

        public DSDocGia SelectDocGia_MaDocGia(string MaDocGia)
        {
            string procName = "SelectDocGia_MaDocGia";

            SqlCommand cmd = new SqlCommand();
            ConnectSQL(ref cn, procName, ref cmd);
            DataSet ds = new DataSet();
            //List<DSDocGia> dsDocGia = new List<DSDocGia>();
            cmd.Parameters.AddWithValue("@MaDocGia", MaDocGia);
            try
            {
                cn.Open();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(ds);

                DSDocGia temp = new DSDocGia();
                temp.MaDocGia = ds.Tables[0].Rows[0][0].ToString();
                temp.HoTenDocGia = ds.Tables[0].Rows[0][1].ToString();
                temp.TenLoaiDocGia = ds.Tables[0].Rows[0][2].ToString();
                temp.NgaySinh = DateTime.Parse(ds.Tables[0].Rows[0][3].ToString());
                temp.DiaChi = ds.Tables[0].Rows[0][4].ToString();
                temp.Email = ds.Tables[0].Rows[0][5].ToString();
                temp.NgayLapThe = DateTime.Parse(ds.Tables[0].Rows[0][6].ToString());
                temp.NgayHetHan = DateTime.Parse(ds.Tables[0].Rows[0][7].ToString());
                temp.TienNo = Int32.Parse(ds.Tables[0].Rows[0][8].ToString());

                cn.Close();
                return temp;
            }
            catch
            {
                return null;
            }
        }

        public List<DSTimSach> SelectSach_TenDauSach(string TenDauSach)
        {
            string procName = "SelectSach_TenDauSach";

            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            DataSet ds = new DataSet();
            List<DSTimSach> dsSach = new List<DSTimSach>();

            cmd.Parameters.AddWithValue("@TenDauSach", TenDauSach);
            try
            {
                cn.Open();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    DSTimSach temp = new DSTimSach();
                    temp.MaSach = ds.Tables[0].Rows[i][0].ToString();
                    temp.MaDauSach = ds.Tables[0].Rows[i][1].ToString();
                    temp.TenDauSach = ds.Tables[0].Rows[i][2].ToString();
                    temp.HoTenTacGia = ds.Tables[0].Rows[i][3].ToString();
                    temp.TenTheLoai = ds.Tables[0].Rows[i][4].ToString();
                    temp.NhaXuatBan = ds.Tables[0].Rows[i][5].ToString();
                    temp.NamXuatBan = Int32.Parse(ds.Tables[0].Rows[i][6].ToString());
                    temp.DonGia = Int32.Parse(ds.Tables[0].Rows[i][7].ToString());

                    dsSach.Add(temp);
                }

                cn.Close();
                return dsSach;
            }
            catch
            {
                return null;
            }
        }

        public List<DSTimSach> SelectSach_NhaXuatBan(string NhaXuatBan)
        {
            string procName = "SelectSach_NhaXuatBan";

            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            DataSet ds = new DataSet();
            List<DSTimSach> dsSach = new List<DSTimSach>();

            cmd.Parameters.AddWithValue("@NhaXuatBan", NhaXuatBan);
            try
            {
                cn.Open();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    DSTimSach temp = new DSTimSach();
                    temp.MaSach = ds.Tables[0].Rows[i][0].ToString();
                    temp.MaDauSach = ds.Tables[0].Rows[i][1].ToString();
                    temp.TenDauSach = ds.Tables[0].Rows[i][2].ToString();
                    temp.HoTenTacGia = ds.Tables[0].Rows[i][3].ToString();
                    temp.TenTheLoai = ds.Tables[0].Rows[i][4].ToString();
                    temp.NhaXuatBan = ds.Tables[0].Rows[i][5].ToString();
                    temp.NamXuatBan = Int32.Parse(ds.Tables[0].Rows[i][6].ToString());
                    temp.DonGia = Int32.Parse(ds.Tables[0].Rows[i][7].ToString());

                    dsSach.Add(temp);
                }

                cn.Close();
                return dsSach;
            }
            catch
            {
                return null;
            }
        }

        public List<DSTimSach> SelectSach_TheLoai(string TenTheLoai)
        {
            string procName = "SelectSach_TheLoai";

            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            DataSet ds = new DataSet();
            List<DSTimSach> dsSach = new List<DSTimSach>();

            cmd.Parameters.AddWithValue("@TenTheLoai", TenTheLoai);
            try
            {
                cn.Open();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    DSTimSach temp = new DSTimSach();
                    temp.MaSach = ds.Tables[0].Rows[i][0].ToString();
                    temp.MaDauSach = ds.Tables[0].Rows[i][1].ToString();
                    temp.TenDauSach = ds.Tables[0].Rows[i][2].ToString();
                    temp.HoTenTacGia = ds.Tables[0].Rows[i][3].ToString();
                    temp.TenTheLoai = ds.Tables[0].Rows[i][4].ToString();
                    temp.NhaXuatBan = ds.Tables[0].Rows[i][5].ToString();
                    temp.NamXuatBan = Int32.Parse(ds.Tables[0].Rows[i][6].ToString());
                    temp.DonGia = Int32.Parse(ds.Tables[0].Rows[i][7].ToString());

                    dsSach.Add(temp);
                }

                cn.Close();
                return dsSach;
            }
            catch
            {
                return null;
            }
        }

        public List<DSBCTraTre> SelectBCTraTre(DateTime NgayTra)
        {
            string procName = "SelectBCTraTre";

            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            DataSet ds = new DataSet();
            List<DSBCTraTre> dsBCTraTre = new List<DSBCTraTre>();

            cmd.Parameters.AddWithValue("@NgayTra", NgayTra);
            try
            {
                cn.Open();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    DSBCTraTre temp = new DSBCTraTre();
                    temp.MaCuonSach = ds.Tables[0].Rows[i][0].ToString();
                    temp.TenDauSach = ds.Tables[0].Rows[i][1].ToString();
                    temp.NgayMuon = DateTime.Parse(ds.Tables[0].Rows[i][2].ToString());
                    temp.NgayPhaiTra = DateTime.Parse(ds.Tables[0].Rows[i][3].ToString());
                    temp.NgayTra = DateTime.Parse(ds.Tables[0].Rows[i][4].ToString());
                    temp.SoNgayMuon = Int32.Parse(ds.Tables[0].Rows[i][5].ToString());

                    dsBCTraTre.Add(temp);
                }

                cn.Close();
                return dsBCTraTre;
            }
            catch
            {
                return null;
            }
        }

        public string SelectTheLoai_TenTheLoai(string TenTheLoai)
        {
            string procName = "SelectTheLoai_TenTheLoai";

            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            DataSet ds = new DataSet();

            cmd.Parameters.AddWithValue("@TenTheLoai", TenTheLoai);
            try
            {
                cn.Open();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(ds);
                string temp;
                temp = ds.Tables[0].Rows[0][0].ToString();
                

                cn.Close();
                return temp;
            }
            catch
            {
                return null;
            }
        }

        public string SelectMaLoaiDocGia(string TenLoaiDocGia)
        {
            string procName = "SelectMaLoaiDocGia";

            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            DataSet ds = new DataSet();
            cmd.Parameters.AddWithValue("@TenLoaiDocGia", TenLoaiDocGia);
            try
            {
                cn.Open();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(ds);
                string temp;
                temp = ds.Tables[0].Rows[0][0].ToString();
                cn.Close();
                return temp;
            }
            catch
            {
                return null;
            }
        }

        public string SelectTenLoaiDocGia(string MaLoaiDocGia)
        {
            string procName = "SelectTenLoaiDocGia";

            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            DataSet ds = new DataSet();
            cmd.Parameters.AddWithValue("@MaLoaiDocGia", MaLoaiDocGia);
            try
            {
                cn.Open();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(ds);
                string temp;
                temp = ds.Tables[0].Rows[0][0].ToString();
                cn.Close();
                return temp;
            }
            catch
            {
                return null;
            }
        }

        public string SelectMaTheLoai(string TenTheLoai)
        {
            string procName = "SelectMaTheLoai";

            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            DataSet ds = new DataSet();
            cmd.Parameters.AddWithValue("@TenTheLoai", TenTheLoai);
            try
            {
                cn.Open();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(ds);
                string temp;
                temp = ds.Tables[0].Rows[0][0].ToString();
                cn.Close();
                return temp;
            }
            catch
            {
                return null;
            }
        }

        public string SelectMaSach(string MaDauSach, int NamXuatBan, string NhaXuatBan, int DonGia)
        {
            string procName = "SelectMaSach";

            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            DataSet ds = new DataSet();
            cmd.Parameters.AddWithValue("@MaDauSach", MaDauSach);
            cmd.Parameters.AddWithValue("@NamXuatBan", NamXuatBan);
            cmd.Parameters.AddWithValue("@NhaXuatBan", NhaXuatBan);
            cmd.Parameters.AddWithValue("@DonGia", DonGia);

            try
            {
                cn.Open();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(ds);
                string temp;
                temp = ds.Tables[0].Rows[0][0].ToString();
                cn.Close();
                return temp;
            }
            catch
            {
                return null;
            }
        }

        public List<string> SelectTenDauSach_DauSach(string TenTheLoai)
        {
            string procName = "SelectTenDauSach_DauSach";

            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            DataSet ds = new DataSet();
            cmd.Parameters.AddWithValue("@TenTheLoai", TenTheLoai);

            List<string> dsTenDauSach = new List<string>();
            try
            {
                cn.Open();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    string temp;
                    temp = ds.Tables[0].Rows[i][0].ToString();

                    dsTenDauSach.Add(temp);
                }

                cn.Close();
                return dsTenDauSach;
            }
            catch
            {
                return null;
            }
        }

        public List<string> SelectTenTheLoai_DauSach(string TenDauSach)
        {
            string procName = "SelectTenTheLoai_DauSach";

            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            DataSet ds = new DataSet();
            cmd.Parameters.AddWithValue("@TenDauSach", TenDauSach);

            List<string> dsTenTheLoai = new List<string>();
            try
            {
                cn.Open();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    string temp;
                    temp = ds.Tables[0].Rows[i][0].ToString();

                    dsTenTheLoai.Add(temp);
                }

                cn.Close();
                return dsTenTheLoai;
            }
            catch
            {
                return null;
            }
        }

        public List<string> SelectTenTacGia_DauSach(string MaDauSach)
        {
            string procName = "SelectTenTacGia_DauSach";

            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            DataSet ds = new DataSet();
            cmd.Parameters.AddWithValue("@MaDauSach", MaDauSach);

            List<string> dsTenTheLoai = new List<string>();
            try
            {
                cn.Open();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    string temp;
                    temp = ds.Tables[0].Rows[i][0].ToString();

                    dsTenTheLoai.Add(temp);
                }

                cn.Close();
                return dsTenTheLoai;
            }
            catch
            {
                return null;
            }
        }

        public string SelectMaDauSach(string TenDauSach, string MaTheLoai)
        {
            string procName = "SelectMaDauSach";

            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            DataSet ds = new DataSet();
            cmd.Parameters.AddWithValue("@TenDauSach", TenDauSach);
            cmd.Parameters.AddWithValue("@MaTheLoai", MaTheLoai);

            try
            {
                cn.Open();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(ds);

                string temp;
                temp = ds.Tables[0].Rows[0][0].ToString();
                cn.Close();
                return temp;
            }
            catch
            {
                return null;
            }
        }

        public string SelectTenTacGia(string MaTacGia)
        {
            string procName = "SelectMaDauSach";

            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            DataSet ds = new DataSet();
            cmd.Parameters.AddWithValue("@MaTacGia", MaTacGia);

            try
            {
                cn.Open();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(ds);

                string temp;
                temp = ds.Tables[0].Rows[0][0].ToString();
                cn.Close();
                return temp;
            }
            catch
            {
                return null;
            }
        }

        public List<string> SelectMaTacGia_CTTG(string MaDauSach)
        {
            string procName = "SelectMaTacGia_CTTG";

            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            DataSet ds = new DataSet();
            cmd.Parameters.AddWithValue("@MaDauSach", MaDauSach);

            List<string> dsMaTacGia = new List<string>();
            try
            {
                cn.Open();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    string temp;
                    temp = ds.Tables[0].Rows[i][0].ToString();

                    dsMaTacGia.Add(temp);
                }

                cn.Close();
                return dsMaTacGia;
            }
            catch
            {
                return null;
            }
        }

        public List<string> SelectTenTacGia_CTTG(string MaDauSach)
        {
            string procName = "SelectTenTacGia_CTTG";

            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            DataSet ds = new DataSet();
            cmd.Parameters.AddWithValue("@MaDauSach", MaDauSach);

            List<string> dsMaTacGia = new List<string>();
            try
            {
                cn.Open();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    string temp;
                    temp = ds.Tables[0].Rows[i][0].ToString();

                    dsMaTacGia.Add(temp);
                }

                cn.Close();
                return dsMaTacGia;
            }
            catch
            {
                return null;
            }
        }

        public string SelectMatKhau(string MaDocGia)
        {
            string procName = "SelectMatKhau";

            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            DataSet ds = new DataSet();
            cmd.Parameters.AddWithValue("@MaDocGia", MaDocGia);

            try
            {
                cn.Open();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(ds);

                string temp;
                temp = ds.Tables[0].Rows[0][0].ToString();
                cn.Close();
                return temp;
            }
            catch
            {
                return "-1";
            }
        }

        public int SelectDoiMatKhau(string MaDocGia)
        {
            string procName = "SelectDoiMatKhau";

            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            DataSet ds = new DataSet();
            cmd.Parameters.AddWithValue("@MaDocGia", MaDocGia);

            try
            {
                cn.Open();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(ds);

                int temp;
                temp = Int32.Parse(ds.Tables[0].Rows[0][0].ToString());
                cn.Close();
                return temp;
            }
            catch
            {
                return -1;
            }
        }

        public string SelectMaTacGia(string HoTenTacGia)
        {
            string procName = "SelectMaTacGia";

            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            DataSet ds = new DataSet();
            cmd.Parameters.AddWithValue("@HoTenTacGia", HoTenTacGia);

            try
            {
                cn.Open();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(ds);

                string temp;
                temp = ds.Tables[0].Rows[0][0].ToString();
                cn.Close();
                return temp;
            }
            catch
            {
                return null;
            }
        }

        public int SelectTongTien_PhieuNhap(string MaPhieuNhap)
        {
            string procName = "SelectTongTien_PhieuNhap";

            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            DataSet ds = new DataSet();
            cmd.Parameters.AddWithValue("@MaPhieuNhap", MaPhieuNhap);

            try
            {
                cn.Open();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(ds);

                int temp;
                temp = Int32.Parse(ds.Tables[0].Rows[0][0].ToString());
                cn.Close();
                return temp;
            }
            catch
            {
                return 0;
            }
        }

        public int SelectSoLuong_Sach(string MaSach)
        {
            string procName = "SelectSoLuong_Sach";

            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            DataSet ds = new DataSet();
            cmd.Parameters.AddWithValue("@MaSach", MaSach);

            try
            {
                cn.Open();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(ds);

                int temp;
                temp = Int32.Parse(ds.Tables[0].Rows[0][0].ToString());
                cn.Close();
                return temp;
            }
            catch
            {
                return 0;
            }
        }

        public string SelectMaSach_CuonSach(string MaCuonSach)
        {
            string procName = "SelectMaSach_CuonSach";

            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            DataSet ds = new DataSet();
            cmd.Parameters.AddWithValue("@MaCuonSach", MaCuonSach);

            try
            {
                cn.Open();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(ds);

                string temp;
                temp = ds.Tables[0].Rows[0][0].ToString();
                cn.Close();
                return temp;
            }
            catch
            {
                return null;
            }
        }

        public string SelectTenDocGia(string MaDocGia)
        {
            string procName = "SelectTenDocGia";

            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            DataSet ds = new DataSet();
            cmd.Parameters.AddWithValue("@MaDocGia", MaDocGia);

            try
            {
                cn.Open();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(ds);

                string temp;
                temp = ds.Tables[0].Rows[0][0].ToString();
                cn.Close();
                return temp;
            }
            catch
            {
                return null;
            }
        }

        public List<string> SelectMaCuonSach_TinhTrang(int TinhTrang)
        {
            string procName = "SelectMaCuonSach_TinhTrang";

            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            DataSet ds = new DataSet();
            cmd.Parameters.AddWithValue("@TinhTrang", TinhTrang);

            List<string> dsMaTacGia = new List<string>();
            try
            {
                cn.Open();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    string temp;
                    temp = ds.Tables[0].Rows[i][0].ToString();

                    dsMaTacGia.Add(temp);
                }

                cn.Close();
                return dsMaTacGia;
            }
            catch
            {
                return null;
            }
        }

        public int SelectSoSachMuon(string MaPhieuMuon)
        {
            string procName = "SelectSoSachMuon";

            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            DataSet ds = new DataSet();
            cmd.Parameters.AddWithValue("@MaPhieuMuon", MaPhieuMuon);

            try
            {
                cn.Open();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(ds);

                int temp;
                temp = Int32.Parse(ds.Tables[0].Rows[0][0].ToString());
                cn.Close();
                return temp;
            }
            catch
            {
                return 0;
            }
        }

        public int SelectGiaTriThamSo(string TenThamSo)
        {
            string procName = "SelectGiaTriThamSo";

            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            DataSet ds = new DataSet();
            cmd.Parameters.AddWithValue("@TenThamSo", TenThamSo);

            try
            {
                cn.Open();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(ds);

                int temp;
                temp = Int32.Parse(ds.Tables[0].Rows[0][0].ToString());
                cn.Close();
                return temp;
            }
            catch
            {
                return 0;
            }
        }

        public DateTime SelectNgayHetHan_DocGia(string MaDocGia)
        {
            string procName = "SelectNgayHetHan_DocGia";

            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            DataSet ds = new DataSet();
            cmd.Parameters.AddWithValue("@MaDocGia", MaDocGia);

            try
            {
                cn.Open();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(ds);

                DateTime temp;
                temp = DateTime.Parse(ds.Tables[0].Rows[0][0].ToString());
                cn.Close();
                return temp;
            }
            catch
            {
                DateTime ex = new DateTime();
                ex.AddDays(1);
                ex.AddMonths(1);
                ex.AddYears(1);
                return ex;
            }
        }

        public int SelectTinhTrangCuonSach(string MaCuonSach)
        {
            string procName = "SelectTinhTrangCuonSach";

            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            DataSet ds = new DataSet();
            cmd.Parameters.AddWithValue("@MaCuonSach", MaCuonSach);

            try
            {
                cn.Open();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(ds);

                int temp=-1;
                temp = int.Parse(ds.Tables[0].Rows[0][0].ToString());
                cn.Close();
                return temp;
            }
            catch
            {
                return -1;
            }
        }

        public int SelectTinhTrangPhieuMuon(string MaCuonSach, string MaPhieuMuon)
        {
            string procName = "SelectTinhTrangPhieuMuon";

            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            DataSet ds = new DataSet();
            cmd.Parameters.AddWithValue("@MaCuonSach", MaCuonSach);
            cmd.Parameters.AddWithValue("@MaPhieuMuon", MaPhieuMuon);

            try
            {
                cn.Open();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(ds);

                int temp = -1;
                temp = int.Parse(ds.Tables[0].Rows[0][0].ToString());
                cn.Close();
                return temp;
            }
            catch
            {
                return -1;
            }
        }

        public int SelectSoSachDangMuon_DocGia(string MaDocGia)
        {
            string procName = "SelectSoSachDangMuon_DocGia";

            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            DataSet ds = new DataSet();
            cmd.Parameters.AddWithValue("@MaDocGia", MaDocGia);

            try
            {
                cn.Open();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(ds);

                int temp = -1;
                temp = int.Parse(ds.Tables[0].Rows[0][0].ToString());
                cn.Close();
                return temp;
            }
            catch
            {
                return -1;
            }
        }

        public List<DSPhieuMuon> SelectPhieuMuon_MaDocGia(string MaDocGia)
        {
            string procName = "SelectPhieuMuon_MaDocGia";

            SqlCommand cmd = new SqlCommand();
            ConnectSQL(ref cn, procName, ref cmd);
            DataSet ds = new DataSet();
            List<DSPhieuMuon> dsPhieuMuon = new List<DSPhieuMuon>();

            cmd.Parameters.AddWithValue("@MaDocGia", MaDocGia);

            try
            {
                cn.Open();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    DSPhieuMuon temp = new DSPhieuMuon();

                    temp.MaPhieuMuon = ds.Tables[0].Rows[i][0].ToString();
                    temp.MaDocGia = ds.Tables[0].Rows[i][1].ToString();
                    temp.HoTenDocGia = ds.Tables[0].Rows[i][2].ToString();
                    temp.MaCuonSach = ds.Tables[0].Rows[i][3].ToString();
                    temp.TenDauSach = ds.Tables[0].Rows[i][4].ToString();
                    temp.TenTheLoai = ds.Tables[0].Rows[i][5].ToString();
                    temp.NhaXuatBan = ds.Tables[0].Rows[i][6].ToString();
                    temp.NamXuatBan = Int32.Parse(ds.Tables[0].Rows[i][7].ToString());
                    temp.NgayMuon = DateTime.Parse(ds.Tables[0].Rows[i][8].ToString());
                    temp.NgayPhaiTra = DateTime.Parse(ds.Tables[0].Rows[i][9].ToString());

                    dsPhieuMuon.Add(temp);
                }

                cn.Close();
                return dsPhieuMuon;
            }
            catch
            {
                return null;
            }
        }

        public List<DSBCMuonSach> SelectBCMuonSach(string MaBCMS)
        {
            string procName = "SelectBCMuonSach";

            SqlCommand cmd = new SqlCommand();
            ConnectSQL(ref cn, procName, ref cmd);
            DataSet ds = new DataSet();
            List<DSBCMuonSach> dsPhieuMuon = new List<DSBCMuonSach>();

            cmd.Parameters.AddWithValue("@MaBCMS", MaBCMS);
            try
            {
                cn.Open();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    DSBCMuonSach temp = new DSBCMuonSach();

                    temp.MaTheLoai = ds.Tables[0].Rows[i][0].ToString();
                    temp.TenTheLoai = ds.Tables[0].Rows[i][1].ToString();
                    temp.Thang = Int32.Parse(ds.Tables[0].Rows[i][2].ToString());
                    temp.Nam = Int32.Parse(ds.Tables[0].Rows[i][3].ToString());
                    temp.LuotMuon = Int32.Parse(ds.Tables[0].Rows[i][4].ToString());
                    temp.TongLuotMuon = Int32.Parse(ds.Tables[0].Rows[i][5].ToString());
                    temp.TiLe = float.Parse(ds.Tables[0].Rows[i][6].ToString());

                    dsPhieuMuon.Add(temp);
                }

                cn.Close();
                return dsPhieuMuon;
            }
            catch
            {
                return null;
            }
        }

        public DateTime SelectNgayPhaiTra_PhieuMuon(string MaPhieuMuon)
        {
            string procName = "SelectNgayPhaiTra_PhieuMuon";

            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            DataSet ds = new DataSet();
            cmd.Parameters.AddWithValue("@MaPhieuMuon", MaPhieuMuon);

            try
            {
                cn.Open();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(ds);

                DateTime temp;
                temp = DateTime.Parse(ds.Tables[0].Rows[0][0].ToString());
                cn.Close();
                return temp;
            }
            catch
            {
                DateTime ex = new DateTime();
                ex.AddDays(1);
                ex.AddMonths(1);
                ex.AddYears(1);
                return ex;
            }
        }

        public DateTime SelectNgayMuon_PhieuMuon(string MaPhieuMuon)
        {
            string procName = "SelectNgayMuon_PhieuMuon";

            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            DataSet ds = new DataSet();
            cmd.Parameters.AddWithValue("@MaPhieuMuon", MaPhieuMuon);

            try
            {
                cn.Open();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(ds);

                DateTime temp;
                temp = DateTime.Parse(ds.Tables[0].Rows[0][0].ToString());
                cn.Close();
                return temp;
            }
            catch
            {
                DateTime ex = new DateTime();
                ex.AddDays(1);
                ex.AddMonths(1);
                ex.AddYears(1);
                return ex;
            }
        }

        public int SelectTienNo_DocGia(string MaDocGia)
        {
            string procName = "SelectTienNo_DocGia";

            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            DataSet ds = new DataSet();
            cmd.Parameters.AddWithValue("@MaDocGia", MaDocGia);

            try
            {
                cn.Open();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(ds);

                int temp = -1;
                temp = int.Parse(ds.Tables[0].Rows[0][0].ToString());
                cn.Close();
                return temp;
            }
            catch
            {
                return -1;
            }
        }

        public int SelectTienPhat_CTPT(string MaPhieuTra, string MaCuonSach)
        {
            string procName = "SelectTienPhat_CTPT";

            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            DataSet ds = new DataSet();
            cmd.Parameters.AddWithValue("@MaPhieuTra", MaPhieuTra);
            cmd.Parameters.AddWithValue("@MaCuonSach", MaCuonSach);

            try
            {
                cn.Open();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(ds);

                int temp = -1;
                temp = int.Parse(ds.Tables[0].Rows[0][0].ToString());
                cn.Close();
                return temp;
            }
            catch
            {
                return -1;
            }
        }

        public List<string> SelectMaSach_MuonSach(string MaDocGia)
        {
            string procName = "SelectMaSach_MuonSach";
            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            DataSet ds = new DataSet();
            cmd.Parameters.AddWithValue("@MaDocGia", MaDocGia);

            List<string> dsMaTacGia = new List<string>();
            try
            {
                cn.Open();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    string temp;
                    temp = ds.Tables[0].Rows[i][0].ToString();

                    dsMaTacGia.Add(temp);
                }

                cn.Close();
                return dsMaTacGia;
            }
            catch
            {
                return null;
            }
        }
        //Moi Them
        public string SelectPhieuTra_MaDocGia(string MaPhieuTra)
        {
            string procName = "SelectPhieuTra_MaDocGia";

            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            DataSet ds = new DataSet();

            cmd.Parameters.AddWithValue("@MaPhieuTra", MaPhieuTra);
            try
            {
                cn.Open();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(ds);
                string temp;
                temp = ds.Tables[0].Rows[0][0].ToString();


                cn.Close();
                return temp;
            }
            catch
            {
                return null;
            }
        }

        public int CountTongLuotMuon(int Thang, int Nam)
        {
            string procName = "CountTongLuotMuon";

            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            DataSet ds = new DataSet();
            cmd.Parameters.AddWithValue("@Thang", Thang);
            cmd.Parameters.AddWithValue("@Nam", Nam);

            try
            {
                cn.Open();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(ds);

                int temp = -1;
                temp = int.Parse(ds.Tables[0].Rows[0][0].ToString());
                cn.Close();
                return temp;
            }
            catch
            {
                return -1;
            }
        }

        public int CountLuotMuonTheoTheLoai(int Thang, int Nam, string MaTheLoai)
        {
            string procName = "CountLuotMuonTheoTheLoai";

            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            DataSet ds = new DataSet();
            cmd.Parameters.AddWithValue("@Thang", Thang);
            cmd.Parameters.AddWithValue("@Nam", Nam);
            cmd.Parameters.AddWithValue("@MaTheLoai", MaTheLoai);

            try
            {
                cn.Open();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(ds);

                int temp = -1;
                temp = int.Parse(ds.Tables[0].Rows[0][0].ToString());
                cn.Close();
                return temp;
            }
            catch
            {
                return -1;
            }
        }

        public string SelectMaLoaiNhanVien(string TenLoaiNhanVien)
        {
            string procName = "SelectMaLoaiNhanVien";

            SqlCommand cmd = new SqlCommand(); 

            ConnectSQL(ref cn, procName, ref cmd);

            DataSet ds = new DataSet();
            cmd.Parameters.AddWithValue("@TenLoaiNhanVien", TenLoaiNhanVien);
            try
            {
                cn.Open();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(ds);
                string temp;
                temp = ds.Tables[0].Rows[0][0].ToString();
                cn.Close();
                return temp;
            }
            catch
            {
                return null;
            }
        }

        public int SelectDoiMatKhau_NhanVien(string MaNhanVien)
        {
            string procName = "SelectDoiMatKhau_NhanVien";

            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            DataSet ds = new DataSet();
            cmd.Parameters.AddWithValue("@MaNhanVien", MaNhanVien);
            try
            {
                cn.Open();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(ds);
                int temp;
                temp = Int32.Parse(ds.Tables[0].Rows[0][0].ToString());
                cn.Close();
                return temp;
            }
            catch
            {
                return -1;
            }
        }

        public string SelectMatKhau_NhanVien(string MaNhanVien)
        {
            string procName = "SelectMatKhau_NhanVien";

            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            DataSet ds = new DataSet();
            cmd.Parameters.AddWithValue("@MaNhanVien", MaNhanVien);

            try
            {
                cn.Open();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(ds);

                string temp;
                temp = ds.Tables[0].Rows[0][0].ToString();
                cn.Close();
                return temp;
            }
            catch
            {
                return "-1";
            }
        }

        public string SelectLoaiNhanVien_NhanVien(string MaNhanVien)
        {
            string procName = "SelectLoaiNhanVien_NhanVien";

            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            DataSet ds = new DataSet();
            cmd.Parameters.AddWithValue("@MaNhanVien", MaNhanVien);
            try
            {
                cn.Open();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(ds);
                string temp;
                temp = ds.Tables[0].Rows[0][0].ToString();
                cn.Close();
                return temp;
            }
            catch
            {
                return null;
            }
        }

        public DSNhanVien SelectNhanVien_MaNhanVien(string MaNhanVien)
        {
            string procName = "SelectNhanVien_MaNhanVien";

            SqlCommand cmd = new SqlCommand();
            ConnectSQL(ref cn, procName, ref cmd);
            DataSet ds = new DataSet();
            //List<DSDocGia> dsDocGia = new List<DSDocGia>();
            cmd.Parameters.AddWithValue("@MaNhanVien", MaNhanVien);
            try
            {
                cn.Open();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(ds);

                DSNhanVien temp = new DSNhanVien();
                temp.MaNhanVien = ds.Tables[0].Rows[0][0].ToString();
                temp.HoTenNhanVien = ds.Tables[0].Rows[0][1].ToString();
                temp.MaLoaiNhanVien = ds.Tables[0].Rows[0][2].ToString();
                temp.TenLoaiNhanVien = ds.Tables[0].Rows[0][3].ToString();

                cn.Close();
                return temp;
            }
            catch
            {
                return null;
            }
        }

        public DateTime SelectNgayHetHan(string MaDocGia)
        {
            string procName = "SelectNgayHetHan";

            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            DataSet ds = new DataSet();
            cmd.Parameters.AddWithValue("@MaDocGia", MaDocGia);

            try
            {
                cn.Open();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(ds);

                DateTime temp;
                temp = DateTime.Parse(ds.Tables[0].Rows[0][0].ToString());
                cn.Close();
                return temp;
            }
            catch
            {
                DateTime ex = new DateTime();
                ex.AddDays(1);
                ex.AddMonths(1);
                ex.AddYears(1);
                return ex;
            }
        }
    }
}